package com.cordys.opensource.crom;

import java.util.Enumeration;

import com.cordys.cpc.bsf.busobject.BusObject;
import com.cordys.cpc.bsf.busobject.BusObjectIterator;
import com.cordys.cpc.bsf.busobject.exception.BsfRuntimeException;
import com.cordys.cpc.bsf.classinfo.ClassInfo;
import com.cordys.cpc.bsf.classinfo.RelationInfo_Composite;
import com.cordys.cpc.bsf.classinfo.RelationInfo_FK;
import com.cordys.cpc.bsf.relation.MultiRelation;
import com.cordys.cpc.bsf.relation.SingleRelation;
import com.cordys.opensource.crom.sql.Column;
import com.cordys.opensource.crom.sql.Order;
import com.cordys.opensource.crom.sql.Query;
import com.cordys.opensource.crom.sql.Table;
import com.cordys.opensource.crom.sql.Where;
import com.cordys.opensource.crom.sql.Where.Operator;

public class QueryUtils
{
	private static final String ID = "Id";
	/**
	 * Returns a query that searches for the object with it's history. This returns only those
	 * objects that have an actual history (i.e., that have changed over time). If an object has not
	 * changed over time, it will also not be returned here.
	 * This is used specifically in case of Revisioned Objects, where all revisions must be loaded
	 * @param domainClass
	 * @return
	 */
	public static Query getObjectHistoryQuery(Class<? extends BusObject> domainClass)
	{
		if (!BusObjectMetadata.isDomainObject(domainClass))
		{
			throw new RuntimeException("Cannot query a class that is not a domain class "+domainClass);
		}
		
		Query query = new Query();
		query.setResultClass(domainClass);
		
		BusObjectMetadata metadata = BusObjectMetadata.getMetadata(domainClass);
		// Select all columns of DB table.
		Table dbTable = query.getTable(metadata.getDbClass()).selectAllColumns();
		if (BusObjectMetadata.isRevisionedObject(domainClass))
		{
			Table revisionTable = query.getTable(metadata.getDbRevisionClass()).selectAllColumns();
			dbTable.innerJoin(revisionTable, BusObjectHelper.getForeignKeyRelation(metadata.getDbClass(), metadata.getDbRevisionClass()));

			RelationInfo_FK fk = BusObjectHelper.getForeignKeyRelation(metadata.getDbClass(), metadata.getDbRevisionClass());
			String revisionTableColumnName = fk.getRelatedAttributes()[0]; // E.g. SolutionId in SolutionRevision, pointing to Solution.Id
			
			Query nestedQuery = new Query();
			Table revision1Table = nestedQuery.getTable(metadata.getDbRevisionClass(), "Revision1");
			revision1Table.select(RevisionedObject.EXPIRED_ON);
			Where where = nestedQuery.where(revisionTable.getColumn(revisionTableColumnName), Operator.Equals, revision1Table.getColumn(revisionTableColumnName));
			where.and(revision1Table.getColumn(RevisionedObject.EXPIRED_ON), Operator.NotEqual, null);
	 		
			query.whereExists(nestedQuery);
	 		
	 		revisionTable.getColumn(RevisionedObject.EXPIRED_ON).setOrdering(Order.DESCENDING);
		}
		
		return query;
	}

	/**
	 * Returns the object by it's primary key, and loads all the children into the object.
	 * @param <O> Type O is expected to have the DomainObject annotation.
	 * @param keyValue
	 * @param domainClass
	 * @return
	 */
	public static <O extends BusObject> O getFullObject(int keyValue, Class<O> domainClass)
	{
    	DomainObjectQuery<O> doq = DomainObjectQuery.getQuery(domainClass, true);
    	
    	
    	// Extend the query with the where clause on the primary key.
    	Query query = doq.getQuery();
    	BusObjectMetadata metadata = BusObjectMetadata.getMetadata(domainClass);
    	
    	Column column = query.getTable(metadata.getDbClass()).getColumn(ID);
		query.where(column, Operator.Equals, keyValue);
		
		return doq.getObject();
	}
	
	public static <O extends BusObject> BusObjectIterator<O> getFullObjects(Class<O> domainClass)
	{
    	DomainObjectQuery<O> doq = DomainObjectQuery.getQuery(domainClass, true);
    	return doq.getObjects();
	}
	
	private static <T extends BusObject> void extendQueryForMappingTable(BusObject parentObject, DomainObjectQuery<? extends BusObject> query, BusObjectMetadata childMetadata)
	{
		Query sqlQuery = query.getQuery();
		
		BusObjectMetadata parentMetadata = BusObjectMetadata.getMetadata(parentObject.getClass());
		
		
		Class<? extends BusObject> childDbClass = childMetadata.getDbClass();
		Class<? extends BusObject> parentDbClass = parentMetadata.getDbClass();
		Class<? extends BusObject> mappingClass = childMetadata.getMappingClass();


		RelationInfo_FK childKey = BusObjectHelper.getForeignKeyRelation(childDbClass, mappingClass);
		RelationInfo_FK parentKey = BusObjectHelper.getForeignKeyRelation(parentDbClass, mappingClass);
		
		Table mappingTable = sqlQuery.getTable(mappingClass);
		Table childTable = sqlQuery.getTable(childDbClass);
		
		String[] parentAttributes = parentKey.getLocalAttributes();
		for (int i=0; i<parentAttributes.length; i++)
		{
			String parentAttributeNameInMappingTable = parentKey.getRelatedAttributes()[i];
			String parentValue = parentObject.getStringProperty(parentAttributes[i]);
			Column column = mappingTable.getColumn(parentAttributeNameInMappingTable);
			sqlQuery.where(column, Operator.Equals, parentValue);
		}

		String[] childAttributes = childKey.getLocalAttributes();
		for (int i=0; i<childAttributes.length; i++)
		{
			String childAttribute = childAttributes[i];
			String childAttributeNameInMappingTable = childKey.getRelatedAttributes()[i];
			Column childColumn = childTable.getColumn(childAttribute);
			Column mappingColumn = mappingTable.getColumn(childAttributeNameInMappingTable);
			sqlQuery.where(childColumn, Operator.Equals, mappingColumn);
		}
	}

	private static <T extends BusObject> void extendQueryOnForeignKey(BusObject parentObject, Table childTable, DomainObjectQuery<T> childQuery, RelationInfo_FK foreignKeyRelation)
	{
		// It is a direct foreign key relation between child and parent. 
		//  Now extend the query with the key information from the parent.
		Query sqlQuery = childQuery.getQuery();
//		Table childTable = childQuery.getFromTable();

		String[] childAttributes = foreignKeyRelation.getLocalAttributes();
		for (int i=0; i<childAttributes.length; i++)
		{
			String parentAttribute = foreignKeyRelation.getRelatedAttributes()[i];
			String parentValue = parentObject.getStringProperty(parentAttribute);
	    	Column column = childTable.getColumn(childAttributes[i]);
//	    	childQuery.selectAttributeForParent(childTable, childAttributes[i]);
			sqlQuery.where(column, Operator.Equals, parentValue);
		}
	}
	
	/**
	 * Loads the child of the specified class on the domain object.
	 * It queries the database to find this child by means of the foreign key relationship with the domain object, if available.
	 * @param parentObject
	 * @param relation The name of the relation 
	 */
	public static <T extends BusObject> void loadRelation(BusObject parentObject, String relation, boolean deep)
	{
		if (parentObject==null)
		{
			return;
		}
		BusObjectMetadata parentMetadata = BusObjectMetadata.getMetadata(parentObject.getClass());
		BusObjectMetadata relationMetadata = parentMetadata.getRelation(relation);
		if (relationMetadata==null)
		{
			throw new BsfRuntimeException("Cannot find a relation named "+relation+" on the Domain Object "+parentObject.getClass().getName());
		}
		DomainObjectQuery<BusObject> doq = new DomainObjectQuery<BusObject>(relationMetadata, deep);
		loadRelation(parentObject, doq);
	}
	
	/**
	 * Loads the child of the specified class on the domain object.
	 * It queries the database to find this child by means of the foreign key relationship with the domain object, if available.
	 * @param parentObject
	 * @param childQuery The domain object query to be used to load the child. This query can be any level deep. It will be extended
	 * with the information that relates the parent object to the child, and then it will be executed.
	 */
	@SuppressWarnings("deprecation")
	public static <T extends BusObject> void loadRelation(BusObject parentObject, DomainObjectQuery<T> childQuery)
	{
		if (parentObject==null)
		{
			return;
		}
		BusObjectMetadata parentMetadata = BusObjectMetadata.getMetadata(parentObject.getClass());
		Class<T> childClass = childQuery.getDomainClass();
		if (!BusObjectMetadata.isDomainObject(childClass))
		{
			// If the child is not a domain object, we cannot query it in the database
			return;
		}
		BusObjectMetadata childMetadata = childQuery.getMetadata();
		if (childMetadata.getParentMetadata()!=parentMetadata) // Compatibility for now...
		{
			// Hmmm. Not quite what we need. Work around this deprecation thing.
			
			childMetadata = parentMetadata.getRelation(childClass);
			if (childMetadata==null)
			{
				throw new BsfRuntimeException("Cannot find a relationship between "+parentObject.getClass().getName()+" and "+childClass.getName());
			}
		}
		
		RelationInfo_Composite relation = childMetadata.getParentRelationship();
		
		RelationInfo_FK foreignKeyRelation = childMetadata.getParentForeignKey();
		if (foreignKeyRelation!=null)
		{
			extendQueryOnForeignKey(parentObject, childQuery.getFromTable(), childQuery, foreignKeyRelation);
		}
		else
		{
			// No need to check whether there is a relationship with the Revision table, all children always link to the parent
			//  table directly.
			extendQueryForMappingTable(parentObject, childQuery, childMetadata);
		}

		if (relation.isMultiOcc())
		{
			BusObjectIterator<?> children = childQuery.getObjects();
			MultiRelation instanceRelation = parentObject._getMultiRelation(relation.getIdentifier(), true);
			while (children.hasMoreElements())
			{
				BusObject object = children.nextElement();
				instanceRelation.addObject(object);
			}
		}
		else
		{
			BusObject childObject = childQuery.getObject();
			if (childObject==null)
			{
				// Child object not found
				return;
			}
			SingleRelation instanceRelation = parentObject._getSingleRelation(relation.getIdentifier(), true);
			instanceRelation.setLocalObject(childObject);
		}
	}

	public static <T extends BusObject> void loadGrandChildren(BusObject parentObject, DomainObjectQuery<T> grandChildQuery)
	{
		if (parentObject==null)
		{
			return;
		}
		BusObjectMetadata parentMetadata = BusObjectMetadata.getMetadata(parentObject.getClass());
		BusObjectMetadata grandChildMetadata = grandChildQuery.getMetadata();
		BusObjectMetadata relationMetadata = grandChildMetadata.getParentMetadata();
		if (relationMetadata==null)
		{
			throw new BsfRuntimeException("The grandChildQuery does not relate to the parentObject of type "+parentObject.getClass().getName()+". Use DomainObjectQuery.getGrandChildQuery to create the query");
		}
		
		if (relationMetadata.getParentMetadata()!=parentMetadata)
		{
			throw new BsfRuntimeException("The grandChildQuery does not relate to the parentObject of type "+parentObject.getClass().getName()+"; It has a parent of type "+relationMetadata.getDomainClass()+", which has parent metadata "+relationMetadata.getParentMetadata()+". Use DomainObjectQuery.getGrandChildQuery to create the query");
		}
		
		
		RelationInfo_Composite grandChildRelation = grandChildMetadata.getParentRelationship();
		if (grandChildRelation==null)
		{
			// There is no relation from the domain object to the specified child class.
			// TODO: throw an exception!
			return;
		}
		
		RelationInfo_FK foreignKeyRelation = grandChildMetadata.getParentForeignKey();
		// First extend the query on the relationship between the child and the grand child.
		if (foreignKeyRelation!=null)
		{
			// No need to check whether there is a relationship with the Revision table, all children always link to the parent
			//  table directly.
			extendQueryOnForeignKey(BusObjectHelper.getRelatedClass(foreignKeyRelation), grandChildQuery, foreignKeyRelation);
		}
		else
		{
			extendQueryForMappingTable(grandChildQuery);
		}

		// And now extend the query on the relationship between the parent and the child.
		foreignKeyRelation = relationMetadata.getParentForeignKey();
		if (foreignKeyRelation!=null)
		{
			Class<? extends BusObject> childDbClass = relationMetadata.getDbClass();
			Table childTable = grandChildQuery.getQuery().getTable(childDbClass, "Parent_"+childDbClass.getSimpleName());
			extendQueryOnForeignKey(parentObject, childTable, grandChildQuery, foreignKeyRelation);
		}
		else
		{
			// No need to check whether there is a relationship with the Revision table, all children always link to the parent
			//  table directly.
			extendQueryForMappingTable(parentObject, grandChildQuery, relationMetadata);
		}
		
		grandChildQuery.loadObjectsInGrandParent(parentObject, relationMetadata.getParentRelationship(), grandChildMetadata.getParentRelationship());
	}
	
	/**
	 * Loads the child of the specified class on the domain object.
	 * It queries the database to find this child by means of the foreign key relationship with the domain object, if available.
	 * @param parentObject
	 * @param childClass The relation that connects the parentObject to the grandChild. E.g., for Ticket as parent, Note can be child, 
	 * and grandChild will be a DomainObjectQuery on User
	 * @param grandChildQuery The domain object query to be used to load the grand children. This query can be any level deep. It will be extended
	 * with the information that relates the parent object to the child and the child to the grand child, and then it will be executed.
	 * @deprecated Use loadGrandChildren(BusObject parentObject, String relation, DomainObjectQuery&lt;T&gt; grandChildQuery) instead
	 */
	@Deprecated
	public static <T extends BusObject, C extends BusObject> void loadGrandChildren(BusObject parentObject, Class<C> childClass, DomainObjectQuery<T> grandChildQuery)
	{
		if (parentObject==null)
		{
			return;
		}
		Class<? extends BusObject> parentClass = parentObject.getClass();
		Class<T> grandChildClass = grandChildQuery.getDomainClass();
		
		if (!BusObjectMetadata.isDomainObject(parentClass) && !BusObjectMetadata.isDomainObject(childClass) && !BusObjectMetadata.isDomainObject(grandChildClass))
		{
			// If the child is not a domain object, we cannot query it in the database
			// TODO: raise an exception
			return;
		}
		
		BusObjectMetadata parentMetadata = BusObjectMetadata.getMetadata(parentClass);
		BusObjectMetadata childMetadata = parentMetadata.getRelation(childClass);
		if (childMetadata==null)
		{
			throw new BsfRuntimeException("There is no relationship between "+parentObject.getClass().getName()+" and "+childClass.getName());
		}
		BusObjectMetadata grandChildMetadata = childMetadata.getRelation(grandChildClass);
		if (grandChildMetadata==null)
		{
			throw new BsfRuntimeException("There is no relationship between "+childClass.getClass().getName()+" and "+grandChildClass.getName());
		}
		
		loadGrandChildren(parentObject, grandChildQuery);
	}
	
	private static <T extends BusObject> void extendQueryOnForeignKey(Class<? extends BusObject> childClass, DomainObjectQuery<T> grandChildQuery, RelationInfo_FK foreignKeyRelation)
	{
		// It is a direct foreign key relation between child and parent. 
		//  Now extend the query with the key information from the parent.
		Query sqlQuery = grandChildQuery.getQuery();
		Table grandChildTable = grandChildQuery.getFromTable();
		try
		{
			@SuppressWarnings("unchecked")
			Class<? extends BusObject> childDbClass = foreignKeyRelation.getRelatedClass();
			Table childTable = sqlQuery.getTable(childDbClass, "Parent_"+childDbClass.getSimpleName());

			String[] grandChildAttributes = foreignKeyRelation.getLocalAttributes();
			String[] childAttributes = foreignKeyRelation.getRelatedAttributes();
			for (int i=0; i<grandChildAttributes.length; i++)
			{
		    	Column grandChildColumn = grandChildTable.getColumn(grandChildAttributes[i]);
		    	Column parentColumn = childTable.getColumn(childAttributes[i]);
//		    	grandChildQuery.selectAttributeForParent(childTable, childAttributes[i]);
				sqlQuery.where(grandChildColumn, Operator.Equals, parentColumn);
			}
			
			// Also select the primary key of the child table, so that we are able to relate it back into the parent
			ClassInfo childClassInfo = BusObject._getClassInfo(childTable.getPersistenceClass());
			String[] childKey = childClassInfo.getUIDElements();
			for (int i=0; i<childKey.length; i++)
			{
		    	grandChildQuery.selectAttributeForParent(childTable, childKey[i]);
			}
		}
		catch (ClassNotFoundException e)
		{
			// TODO Auto-generated catch block
			throw new RuntimeException("This is a strange error");
		}
	}
	
	private static <T extends BusObject> void extendQueryForMappingTable(DomainObjectQuery<T> grandChildQuery)
	{
		BusObjectMetadata grandChildMetadata = grandChildQuery.getMetadata();
		BusObjectMetadata childMetadata = grandChildMetadata.getParentMetadata();
		Class<? extends BusObject> childClass = childMetadata.getDomainClass(); 

		
		Class<? extends BusObject> grandChildDbClass = grandChildMetadata.getDbClass();
		Class<? extends BusObject> childDbClass = childMetadata.getDbClass(); 
		
		// Find a mapping table between child and grandChild
		//  E.g., for Participant and CommunicationType, this will return a query on Participant_CommunicationType
		Class<? extends BusObject> mappingClass = grandChildMetadata.getMappingClass();

		
		if (mappingClass==null && BusObjectMetadata.isRevisionedObject(childClass.getClass()))
		{
			// Find a mapping table between parent's revision table and child
			//  E.g., for Calculation and Keyword, this will return a query on CalculationRevision_Keyword
			childDbClass = childMetadata.getDbRevisionClass();
			mappingClass = grandChildMetadata.getMappingClass();
		}
		if (mappingClass==null)
		{
			// TODO: raise an exception: parent revision class also has no relationship
			return;
		}

		RelationInfo_FK grandChildKey = BusObjectHelper.getForeignKeyRelation(grandChildDbClass, mappingClass);
		RelationInfo_FK childKey = BusObjectHelper.getForeignKeyRelation(childDbClass, mappingClass);
		
		Query sqlQuery = grandChildQuery.getQuery();

		Table childTable = sqlQuery.getTable(childClass);
		Table mappingTable = sqlQuery.getTable(mappingClass);
		Table grandChildTable = grandChildQuery.getFromTable();
		
		// Now extend the query:
		//  where User.Id = Note_User.UserId and Note_User.NoteId = Note.Id
		
		
		// ... "where User.Id = Note_User.UserId"
		String[] grandChildAttributes = grandChildKey.getLocalAttributes();
		for (int i=0; i<grandChildAttributes.length; i++)
		{
			Column grandChildColumn = grandChildTable.getColumn(grandChildAttributes[i]);
			Column mappingColumn = mappingTable.getColumn(grandChildKey.getRelatedAttributes()[i]);
			sqlQuery.where(grandChildColumn, Operator.Equals, mappingColumn);
		}

		// ... and Note_User.NoteId = Note.Id
		String[] childAttributes = childKey.getLocalAttributes();
		for (int i=0; i<childAttributes.length; i++)
		{
			Column parentColumn = childTable.getColumn(childAttributes[i]);
			Column mappingColumn = mappingTable.getColumn(childKey.getRelatedAttributes()[i]);
			sqlQuery.where(mappingColumn, Operator.Equals, parentColumn);
		}
	}	
	
	/**
	 * Goes through the child relations of the domain object, and checks for each child
	 * if it has the MasterData annotation. If so, it will try to find the foreign key relationship
	 * and query for the masterdata object with the foreign key. If it finds the masterdata object,
	 * it will be added to the domain object.
	 * Currently this only works for single-occurence masterdata (so, not for keywords, comments, etc).
	 * @param parentObject
	 */
	@SuppressWarnings("unchecked")
	public static void loadMasterdataChildren(BusObject parentObject)
	{
		ClassInfo domainClassInfo = BusObject._getClassInfo(parentObject.getClass());
		// Also set the names of the child objects. This helps in easy navigation.
		Enumeration<?> relations = domainClassInfo.getRelationInfos();
		while (relations.hasMoreElements())
		{
			Object o = relations.nextElement();
			if (o instanceof RelationInfo_Composite)
			{
				RelationInfo_Composite relation = (RelationInfo_Composite) o;
				try
				{
					Class<BusObject> childClass = relation.getRelatedClass();
					if (!BusObjectMetadata.isMasterDataObject(childClass))
					{
						continue;
					}
					if (childClass == parentObject.getClass()) // Avoid looping to own child, which would load the child itself again.
					{
						continue;
					}
					DomainObjectQuery<BusObject> masterdataDomainQuery = DomainObjectQuery.getChildQuery(parentObject.getClass(), relation.getName(), false);
					loadRelation(parentObject, masterdataDomainQuery);					
				}
				catch (ClassNotFoundException cnfe)
				{
					// Just ignore.
				}
			}
		}
	}
}
